<?php
/**
 * Created by PhpStorm.
 * User: HuangLuSen
 * Date: 2017/5/6
 * Time: 14:49
 */

namespace app\common\traits;

use PHPExcel_Writer_Excel5;
use PHPExcel;
use PHPExcel_Reader_Excel5;
use PHPExcel_Reader_Excel2007;
use PHPExcel_Shared_Date;
use think\Db;

trait Excel
{

    public function exportexcel($data = array(), $title = array(), $filename = '金牌O2O电商管理系统')
    {
        /* 实例化类 */
        $objPHPExcel = new PHPExcel();

        /* 设置输出的excel文件为2007兼容格式 */
        $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        //$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);

        $objActSheet = $objPHPExcel->getActiveSheet();

        /*设置宽度*/
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(5);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(11);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(7);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(10);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(10);
        /* sheet标题 */
        $objActSheet->setTitle($filename);

        //设置表头
        $key = ord("A");
        $key2 = ord("@");//@--64
        foreach ($title as $v) {
//                $colum = chr($key);
            if ($key > ord("Z")) {
                $key2 += 1;
                $key = ord("A");
                $colum = chr($key2) . chr($key);//超过26个字母时才会启用
            } else {
                if ($key2 >= ord("A")) {
                    $colum = chr($key2) . chr($key);//超过26个字母时才会启用
                } else {
                    $colum = chr($key);
                }
            }
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
            $key += 1;
        }

        $column = 2;
        $objActSheet = $objPHPExcel->getActiveSheet();
        foreach ($data as $key => $rows) { //行写入
            $span = ord("A");
            $span2 = ord("@");//@--64
            foreach ($rows as $keyName => $value) {// 列写入
//                    $j = chr($span);
                if ($span > ord("Z")) {
                    $span2 += 1;
                    $span = ord("A");
                    $j = chr($span2) . chr($span);//超过26个字母时才会启用
                } else {
                    if ($span2 >= ord("A")) {
                        $j = chr($span2) . chr($span);//超过26个字母时才会启用
                    } else {
                        $j = chr($span);
                    }
                }
                $objActSheet->setCellValue($j . $column, $value);
                $span++;
            }
            $column++;
        }

        /* 生成到浏览器，提供下载 */
        ob_end_clean();  //清空缓存
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="' . $filename . '.xls"');
        header("Content-Transfer-Encoding:binary");
        $objWriter->save('php://output');
    }

    function impUser($file_name)
    {
        if (!empty($file_name)) {

            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
            if ($extension == 'xlsx') {
                $objReader = new PHPExcel_Reader_Excel2007();
                $objExcel = $objReader->load($file_name, $encode = 'utf-8');
            } else if ($extension == 'xls') {
                $objReader = new PHPExcel_Reader_Excel5();
                $objExcel = $objReader->load($file_name, $encode = 'utf-8');
            } else if ($extension == 'csv') {
                $PHPReader = new PHPExcel_Reader_CSV();

                //默认输入字符集
                $PHPReader->setInputEncoding('GBK');

                //默认的分隔符
                $PHPReader->setDelimiter(',');

                //载入文件
                $objExcel = $PHPReader->load($file_name, $encode = 'utf-8');
            }

            $sheet = $objExcel->getSheet(0);

            $highestRow = $sheet->getHighestRow(); // 取得总行数
            if($highestRow >1000){
                halt('导入记录不能超过1000条');
            }

            $highestColumn = $sheet->getHighestColumn(); // 取得总列数

            $order = new Order();
            $order_error = new Order_error();
            $user = session('admin_user');

            for ($i = 3; $i <= $highestRow; $i++) {
//                  $id[$i][] = (string) $objExcel->getActiveSheet()->getCell("A".$i)->getValue();
//                  $info[$i]['id'] = $id ;
                $jdtime = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($objExcel->getActiveSheet()->getCell("A" . $i)->getValue()));
                $jdtime = strtotime($jdtime);
                if( $jdtime < 0 || !$jdtime ){
                    $jdtime = (string)$objExcel->getActiveSheet()->getCell("A" . $i)->getValue();
                    $jdtime = $this->changeTime($jdtime);
                }
                $phone1 = (string)$objExcel->getActiveSheet()->getCell("D" . $i)->getValue();
                //$phone2 = (string)$objExcel->getActiveSheet()->getCell("E" . $i)->getValue();
                $name = (string)$objExcel->getActiveSheet()->getCell("B" . $i)->getValue();
                //$weixin = (string)$objExcel->getActiveSheet()->getCell("E" . $i)->getValue();
                //$qq = (string)$objExcel->getActiveSheet()->getCell("F" . $i)->getValue();
                // $yctime = $objExcel->getActiveSheet()->getCell("G".$i)->getValue();
                $yctime = gmdate("Y-m-d H:i:s", PHPExcel_Shared_Date::ExcelToPHP($objExcel->getActiveSheet()->getCell("C" . $i)->getValue()));
                $yctime = strtotime($yctime);
                if($yctime < 0 || !$yctime){
                    $yctime = (string)$objExcel->getActiveSheet()->getCell("C" . $i)->getValue();
                    $yctime = $this->changeTime($yctime);
                }
                $yiyuan = (string)$objExcel->getActiveSheet()->getCell("F" . $i)->getValue();
                $shoujikeshi = (string)$objExcel->getActiveSheet()->getCell("G" . $i)->getValue();
                $sjpsry = (string)$objExcel->getActiveSheet()->getCell("H" . $i)->getValue();
                $addr = (string)$objExcel->getActiveSheet()->getCell("I" . $i)->getValue();

                $menzhen = Db::name('leibie')->where(function ($query) use ($shoujikeshi) {
                    $query->where(['status' => 1, 'leibie' => 7, 'name' => $shoujikeshi]);
                })->find();
                $list = Db::name('leibie')->where(function ($query) use ($yiyuan) {
                    $query->where(['status' => 1, 'leibie' => 3, 'name' => $yiyuan]);
                })->find();
                $user = Db::name('users')->where(function ($query) use ($sjpsry) {
                    $query->where(['status' => 1, 'username' => $sjpsry]);
                })->find();
                $tel = false;
                if ($this->isMobile($phone1)) {
                    $phone = Db::name('order')->where(function ($query) use ($phone1, $phone2) {
                        $query->where(['status' => 1, 'maphone' => $phone1])->whereOR(['otherphone' => $phone2]);
                    })->find();
                    $tel = true;
                }
                if($tel == true){
                    if (empty($phone)) {
                        $arr[$i]['jdtime'] = $jdtime;
                        $arr[$i]['name'] =  $name;
                        $arr[$i]['maphone'] = (string)$phone1;
                        $arr[$i]['otherphone'] = (string)$phone2;
                        $arr[$i]['weixin'] = (string)$weixin;
                        $arr[$i]['qq'] = (string)$qq;
                        $arr[$i]['yctime'] = $yctime;
                        $arr[$i]['yiyuan'] = $yiyuan;
                        $arr[$i]['jdry'] = empty($user['id']) ? 0 : $user['id'];
                        $arr[$i]['qd_depar'] = (int)1;
                        $arr[$i]['fenlei'] = (int)83;
                        $arr[$i]['yiyuan'] = $list['id'];
                        $arr[$i]['yiyuan_name'] = $list['name'];
                        $arr[$i]['area'] = $list['dq'];
                        $arr[$i]['menzhen'] = $menzhen['id'];
                        $arr[$i]['menzhen_name'] = $menzhen['name'];
                        $arr[$i]['address'] = $addr;
                        $arr[$i]['ywstate'] = (int)1;
                        $arr[$i]['daoru'] = (int)2;
                    } else {
//                        $order_log = new Order_log();
//                        $log = $order_log->where(['order_id'=>$phone['id']])->find();
//                        if($phone['daoru'] == 2 && $phone['ywstate'] == 1 && empty($log)){
//                            if(empty($phone['yiyuan']) || $phone['yiyuan'] == 0 ){
//                                $yy = empty($list['id'])? 0:$list['id'];
//                            }else{
//                                $yy = $phone['yiyuan'];
//                            }
//                            $gengxindata[$i]['id'] = $phone['id'];
//                            $gengxindata[$i]['jdtime']=$jdtime;
//                            $gengxindata[$i]['yctime']=$yctime;
//                            $gengxindata[$i]['yiyuan']=$yy;
//
//                            $logdata[$i]['order_id']=$phone['id'];
//                            $logdata[$i]['name']=$name;
//                            $logdata[$i]['maphone']=$phone1;
//                            $logdata[$i]['otherphone']=$phone2;
//                            $logdata[$i]['jdtime']=$jdtime;
//                            $logdata[$i]['yctime']=$yctime;
//                            $logdata[$i]['yiyuan']=$yy;
//                            $logdata[$i]['daoru']=2;
//                            $logdata[$i]['operation']=21;
//                            $logdata[$i]['khd']=3;
//                            $logdata[$i]['ip'] = request()->ip();
//                        }

                        $error[$i]['name'] = $name;
                        $error[$i]['maphone'] = $phone1;
                        $error[$i]['otherphone'] = $phone2;
                        $error[$i]['yctime'] = $yctime;
                        $error[$i]['yiyuan_name'] = $yiyuan;
                        $error[$i]['msg'] = '重复';
                    }
                }
            }

//            if($gengxindata != NULL){
//                $order->allowField(true)->saveAll($gengxindata);
//                $order_log->allowField(true)->saveAll($logdata);
//            }
            if ($arr != NULL) {
                $order->allowField(true)->saveAll($arr);
            }
            if ($error != NULL) {
                $info_error = $order_error->allowField(true)->saveAll($error);
            }

            if (!empty($info_error)) {
                if (empty($arr)) {
                    $cards = $error;
                } else {
                    $cards = array_merge($arr, $error);
                }
            } else {
                $cards = $arr;
            }


            return $cards;

            // $this->success('导入成功！');
        } else {
            $this->error("请选择上传的文件");

        }

    }

    function impUserzjs($file_name)
    {
        if (!empty($file_name)) {

            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
            if ($extension == 'xlsx') {
                $objReader = new PHPExcel_Reader_Excel2007();
                $objExcel = $objReader->load($file_name, $encode = 'utf-8');
            } else if ($extension == 'xls') {
                $objReader = new PHPExcel_Reader_Excel5();
                $objExcel = $objReader->load($file_name, $encode = 'utf-8');
            } else if ($extension == 'csv') {
                $PHPReader = new PHPExcel_Reader_CSV();

                //默认输入字符集
                $PHPReader->setInputEncoding('GBK');

                //默认的分隔符
                $PHPReader->setDelimiter(',');

                //载入文件
                $objExcel = $PHPReader->load($file_name, $encode = 'utf-8');
            }

            $sheet = $objExcel->getSheet(0);

            $highestRow = $sheet->getHighestRow(); // 取得总行数

            $highestColumn = $sheet->getHighestColumn(); // 取得总列数



            for ($i = 1; $i <= $highestRow; $i++) {
                $user  = (string)$objExcel->getActiveSheet()->getCell("A" . $i)->getValue();
                $phone = (string)$objExcel->getActiveSheet()->getCell("C" . $i)->getValue();
                $bumen = (string)$objExcel->getActiveSheet()->getCell("B" . $i)->getValue();


                $sid = Db::name('department')->where(function ($query) use ($bumen) {
                    $query->where(['sectionname' => $bumen]);
                })->find();
                $tel = false;
                if ($this->isMobile($phone)) {
                    $tele = Db::name('employee')->where(function ($query) use ($phone) {
                        $query->where(['status' => 1, 'phone' => $phone]);
                    })->find();
                    $tel = true;
                }
                if($tel == true){
                    if(empty($sid)){
                        Db::name('department')->insert(['sectionname'=>$bumen,'fid'=>0,'path'=>0]);
                        $sid = Db::name('department')->where(function ($query) use ($bumen) {
                            $query->where(['sectionname' => $bumen]);
                        })->find();
                    }
                    if (empty($tele)) {
                        $arr[$i]['name']   = $user;
                        $arr[$i]['sid']    =  $sid['id'];
                        $arr[$i]['phone']  = $phone;
                        $arr[$i]['status'] = (int)1;
                    }
                }
            }

            $data = model('employee')->saveAll($arr);
            dump($data);die;


            return '成功';

            // $this->success('导入成功！');
        } else {
            $this->error("请选择上传的文件");

        }

    }

    function isMobile($mobile)
    {
        if (!is_numeric($mobile)) {
            return false;
        }
        return preg_match('#^13[\d]{9}$|^14[5,7]{1}\d{8}$|^15[^4]{1}\d{8}$|^17[0,6,7,8]{1}\d{8}$|^18[\d]{9}$#', $mobile) ? true : false;
    }

    /**替换统一Excel导入的时间格式
     * @param $time
     * @return false|int|mixed
     */
    public function changeTime($time){
        $arr = str_replace(['.','/','年','月','日'], "-", $time);
        $arr = strtotime($arr);
        return $arr;
    }

}